{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import lightgbm as lgb\n",
    "import datetime\n",
    "import math\n",
    "import gc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "source_path = './source_data/'\n",
    "target_path = './target_data/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done loading...\n"
     ]
    }
   ],
   "source": [
    "train = pd.read_csv(source_path + 'train.csv')\n",
    "test = pd.read_csv(source_path + 'test.csv')\n",
    "songs = pd.read_csv(source_path + 'songs.csv')\n",
    "members = pd.read_csv(source_path + 'members.csv',dtype={'bd' : np.uint8},\n",
    "                     parse_dates=['registration_init_time','expiration_date'])\n",
    "songs_extra = pd.read_csv(source_path + 'song_extra_info.csv')\n",
    "print('Done loading...')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 处理歌曲数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 部分歌手数据清洗\n",
    "songs.artist_name[songs['artist_name'] == '爵士輕鬆聽'] = '爵士'\n",
    "songs.artist_name[songs['artist_name'] == '爵士演唱輯'] = '爵士'\n",
    "songs.artist_name[songs['artist_name'] == '爵士演奏輯'] = '爵士'\n",
    "songs.artist_name[songs['artist_name'] == '蓮緣系列-佛曲演唱'] = '清淨身心靈'\n",
    "songs.artist_name[songs['artist_name'] == '群星'] = 'Various'\n",
    "songs.artist_name[songs.artist_name.str.find('Various') >= 0] = 'Various'\n",
    "\n",
    "songs.composer[songs['composer'] == 'Unknow Composer'] = 'no_composer'\n",
    "songs.composer[songs['composer'] == '創時代音樂工作室'] = '創時代音樂製作有限公司'\n",
    "songs.composer[songs['composer'] == '久石譲 (Joe Hisaishi)'] = '久石譲'\n",
    "songs.composer[songs['composer'] == '久石讓'] = '久石譲'\n",
    "songs.composer[songs['composer'] == '莫札特'] = 'Mozart'\n",
    "songs.composer[songs['composer'] == '無'] = 'no_composer'\n",
    "songs.composer[songs['composer'] == '佚名'] = 'no_composer'\n",
    "songs.composer[songs['composer'] == '約翰威廉斯'] = 'John Williams'\n",
    "# 部分作词数据清洗\n",
    "songs.lyricist[songs['lyricist'] == ' '] = 'no_lyricist'\n",
    "songs.lyricist[songs['lyricist'] == '-'] = 'no_lyricist'\n",
    "songs.lyricist[songs['lyricist'] == 'None'] = 'no_lyricist'\n",
    "songs.lyricist[songs['lyricist'] == 'Unknown'] = 'no_lyricist'\n",
    "songs.lyricist[songs['lyricist'] == '—'] = 'no_lyricist'\n",
    "songs.lyricist[songs['lyricist'] == '―'] = 'no_lyricist'\n",
    "songs.lyricist[songs['lyricist'] == '傳統歌謠'] = '傳統'\n",
    "songs.lyricist[songs['lyricist'] == '傳統曲'] = '傳統'\n",
    "songs.lyricist[songs['lyricist'] == '林夕 '] = '林夕'\n",
    "songs.lyricist[songs['lyricist'] == 'Lin Xi'] = '林夕'\n",
    "songs.lyricist[songs['lyricist'] == '秋元 康'] = '秋元康'\n",
    "songs.lyricist[songs['lyricist'] == '秋元　康'] = '秋元康'\n",
    "songs.lyricist[songs['lyricist'] == '秋元 康（Akimoto yasushi）'] = '秋元康'\n",
    "songs.lyricist[songs['lyricist'] == '秋元康'] = '秋元康'\n",
    "songs.lyricist[songs['lyricist'] == '秋元康(Akimoto yasushi)'] = '秋元康'\n",
    "songs.lyricist[songs['lyricist'] == '無'] = 'no_lyricist'\n",
    "songs.lyricist[songs['lyricist'] == '夏奇拉|Luis Fernando Ochoa'] = '夏奇拉'\n",
    "songs.lyricist[songs['lyricist'] == '佚名'] = 'no_lyricist'\n",
    "songs.lyricist[songs['lyricist'] == '畑 亜貴'] = '畑亜貴'\n",
    "songs.lyricist[songs['lyricist'] == '畑　亜貴'] = '畑亜貴'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def same(x,y):\n",
    "    if isinstance(x,str) and isinstance(y,str):\n",
    "        return (x.find(y) + y.find(x)) > -2\n",
    "    return 0\n",
    "songs['artist_composer'] = songs.apply(lambda x:same(x['artist_name'],x['composer']),axis=1).astype(np.int8)\n",
    "songs['artist_lyricist'] = songs.apply(lambda x:same(x['artist_name'],x['lyricist']),axis=1).astype(np.int8)\n",
    "songs['artist_composer_lyricist'] = ((songs.artist_composer + songs.artist_lyricist) == 2).astype(np.int8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 处理歌曲额外信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def isrc_to_year(isrc):\n",
    "    if type(isrc) == str:\n",
    "        if int(isrc[5:7]) > 17:\n",
    "            return 1900 + int(isrc[5:7])\n",
    "        else:\n",
    "            return 2000 + int(isrc[5:7])\n",
    "    else:\n",
    "        return np.nan\n",
    "        \n",
    "songs_extra['song_year'] = songs_extra['isrc'].apply(isrc_to_year)\n",
    "songs_extra['isrc_name'] = songs_extra.name.fillna('no_name') + songs_extra.isrc.fillna('no_isrc')\n",
    "songs_extra.drop(['isrc', 'name'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 处理用户信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "members['membership_days'] = members['expiration_date'].subtract(members['registration_init_time']).dt.days.astype(int)\n",
    "\n",
    "members['registration_year'] = members['registration_init_time'].dt.year\n",
    "members['registration_month'] = members['registration_init_time'].dt.month\n",
    "members['registration_date'] = members['registration_init_time'].dt.day\n",
    "\n",
    "members['expiration_year'] = members['expiration_date'].dt.year\n",
    "members['expiration_month'] = members['expiration_date'].dt.month\n",
    "members['expiration_date'] = members['expiration_date'].dt.day\n",
    "members = members.drop(['registration_init_time'], axis=1)\n",
    "\n",
    "# 年龄\n",
    "members.bd[(members['bd'] < 2) | (members['bd'] > 100)] = 22"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 合并数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data merging...\n",
      "Done merging...\n"
     ]
    }
   ],
   "source": [
    "print('Data merging...')\n",
    "\n",
    "\n",
    "train = train.merge(songs, on='song_id', how='left')\n",
    "test = test.merge(songs, on='song_id', how='left')\n",
    "\n",
    "train = train.merge(members, on='msno', how='left')\n",
    "test = test.merge(members, on='msno', how='left')\n",
    "\n",
    "train = train.merge(songs_extra, on = 'song_id', how = 'left')\n",
    "test = test.merge(songs_extra, on = 'song_id', how = 'left')\n",
    "\n",
    "del members, songs; gc.collect();\n",
    "\n",
    "print('Done merging...')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "n_train = train.shape[0]\n",
    "n_test = test.shape[0]\n",
    "target = train.target\n",
    "test_id = test.id\n",
    "df = train.append(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['target']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 缺失值填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.source_system_tab.fillna('no_source_tab',inplace=True)\n",
    "df.source_screen_name.fillna('no_source_name',inplace=True)\n",
    "df.source_type.fillna('no_source_type',inplace=True)\n",
    "df.artist_composer.fillna(0,inplace=True)\n",
    "df.artist_composer_lyricist.fillna(0,inplace=True)\n",
    "df.artist_lyricist.fillna(0,inplace=True)\n",
    "df.artist_name.fillna('no_artist',inplace=True)\n",
    "df.composer.fillna('no_composer',inplace=True)\n",
    "df.gender.fillna('-1',inplace=True)\n",
    "df.genre_ids.fillna('no_genre_id',inplace=True)\n",
    "df.isrc_name.fillna(df.song_id,inplace=True)\n",
    "df.language.fillna(-1,inplace=True)\n",
    "df.lyricist.fillna('no_lyricist',inplace=True)\n",
    "df.song_year.fillna(2017,inplace=True)\n",
    "df.song_length.fillna(200000,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train = df.iloc[:n_train,:]\n",
    "# test = df.iloc[n_train:,:]\n",
    "# del df\n",
    "# gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 特征处理 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "feature handle....\n",
      "genre_ids done....\n",
      "lyricists_count done....\n",
      "composer_count done....\n",
      "artist_count done....\n",
      "song_lang_boolean done....\n",
      "samllaer_song done....\n"
     ]
    }
   ],
   "source": [
    "print('feature handle....')\n",
    "def genre_id_count(x):\n",
    "    if x == 'no_genre_id':\n",
    "        return 0\n",
    "    else: \n",
    "        return x.count('|') + 1\n",
    "\n",
    "df['genre_ids_count'] = df['genre_ids'].apply(genre_id_count).astype(np.int8)\n",
    "\n",
    "print('genre_ids done....')\n",
    "\n",
    "def lyricist_count(x):\n",
    "    if x == 'no_lyricist':\n",
    "        return 0\n",
    "    else:\n",
    "        return sum(map(x.count, ['|', '/', '\\\\', ';'])) + 1\n",
    "    return sum(map(x.count, ['|', '/', '\\\\', ';']))\n",
    "\n",
    "df['lyricists_count'] = df['lyricist'].apply(lyricist_count).astype(np.int8)\n",
    "print('lyricists_count done....')\n",
    "\n",
    "def composer_count(x):\n",
    "    if x == 'no_composer':\n",
    "        return 0\n",
    "    else:\n",
    "        return sum(map(x.count, ['|', '/', '\\\\', ';'])) + 1\n",
    "df['composer_count'] = df['composer'].apply(composer_count).astype(np.int8)\n",
    "print('composer_count done....')\n",
    "\n",
    "def artist_count(x):\n",
    "    if x == 'no_artist':\n",
    "        return 0\n",
    "    else:\n",
    "        return x.count('and') + x.count(',') + x.count('feat') + x.count('&') + 1\n",
    "\n",
    "df['artist_count'] = df['artist_name'].apply(artist_count).astype(np.int8)\n",
    "print('artist_count done....')\n",
    "\n",
    "def is_featured(x):\n",
    "    if 'feat' in str(x) :\n",
    "        return 1\n",
    "    return 0\n",
    "df['is_featured'] = df['artist_name'].apply(is_featured).astype(np.int8)\n",
    "\n",
    "def song_lang_boolean(x):\n",
    "    if '17.0' in str(x) or '45.0' in str(x):\n",
    "        return 1\n",
    "    return 0\n",
    "df['song_lang_boolean'] = df['language'].apply(song_lang_boolean).astype(np.int8)\n",
    "print('song_lang_boolean done....')\n",
    "\n",
    "_mean_song_length = np.mean(df['song_length'])\n",
    "def smaller_song(x):\n",
    "    if x < _mean_song_length:\n",
    "        return 1\n",
    "    return 0\n",
    "\n",
    "df['smaller_song'] = df['song_length'].apply(smaller_song).astype(np.int8)\n",
    "print('samllaer_song done....')\n",
    "\n",
    "# 群星演唱的歌曲，歌手数设成20\n",
    "df.artist_count[df['artist_name'] == 'Various'] = 20\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count_song_played done....\n"
     ]
    }
   ],
   "source": [
    "# number of times a song has been played before\n",
    "_dict_count_song_played_train = {k: v for k, v in df.iloc[:n_train]['song_id'].value_counts().iteritems()}\n",
    "_dict_count_song_played_test = {k: v for k, v in df.iloc[n_train:]['song_id'].value_counts().iteritems()}\n",
    "def count_song_played(x):\n",
    "    try:\n",
    "        return _dict_count_song_played_train[x]\n",
    "    except KeyError:\n",
    "        try:\n",
    "            return _dict_count_song_played_test[x]\n",
    "        except KeyError:\n",
    "            return 0\n",
    "    \n",
    "df['count_song_played'] = 0\n",
    "tr = df.iloc[:n_train]['song_id'].apply(count_song_played).astype(np.int64)\n",
    "te = df.iloc[n_train:]['song_id'].apply(count_song_played).astype(np.int64)\n",
    "df['count_song_played'] = tr.append(te)\n",
    "del tr,te ;gc.collect()\n",
    "print('count_song_played done....')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count_artist_played done....\n",
      "count_user_played done....\n"
     ]
    }
   ],
   "source": [
    "# number of times the artist has been played\n",
    "_dict_count_artist_played_train = {k: v for k, v in df.iloc[:n_train]['artist_name'].value_counts().iteritems()}\n",
    "_dict_count_artist_played_test = {k: v for k, v in df.iloc[n_train:]['artist_name'].value_counts().iteritems()}\n",
    "def count_artist_played(x):\n",
    "    try:\n",
    "        return _dict_count_artist_played_train[x]\n",
    "    except KeyError:\n",
    "        try:\n",
    "            return _dict_count_artist_played_test[x]\n",
    "        except KeyError:\n",
    "            return 0\n",
    "df['count_artist_played'] = 0\n",
    "tr = df.iloc[:n_train]['artist_name'].apply(count_artist_played).astype(np.int64)\n",
    "te = df.iloc[n_train:]['artist_name'].apply(count_artist_played).astype(np.int64)\n",
    "df['count_artist_played'] = tr.append(te)\n",
    "print('count_artist_played done....')\n",
    "\n",
    "# number of times the user played\n",
    "_dict_count_user_played_train = {k: v for k, v in df.iloc[:n_train]['msno'].value_counts().iteritems()}\n",
    "_dict_count_user_played_test = {k: v for k, v in df.iloc[n_train:]['msno'].value_counts().iteritems()}\n",
    "def count_user_played(x):\n",
    "    try:\n",
    "        return _dict_count_user_played_train[x]\n",
    "    except KeyError:\n",
    "        try:\n",
    "            return _dict_count_user_played_test[x]\n",
    "        except KeyError:\n",
    "            return 0\n",
    "df['count_user_played'] = 0\n",
    "tr = df.iloc[:n_train]['msno'].apply(count_user_played).astype(np.int64)\n",
    "te = df.iloc[n_train:]['msno'].apply(count_user_played).astype(np.int64)\n",
    "df['count_user_played'] = tr.append(te)\n",
    "del tr,te;gc.collect()\n",
    "print('count_user_played done....')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = df.iloc[:n_train,:]\n",
    "test = df.iloc[n_train:,:]\n",
    "train = pd.concat([train,target],axis=1)\n",
    "test = pd.concat([test_id,test],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.to_csv(target_path + 'train.csv',index=False)\n",
    "test.to_csv(target_path + 'test.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
